/**
 * Project: topdt-jbpm
 * Copyright 2013  Top Digital Technology  Co. Ltd.
 * All right reserved.
 */
package com.topdt.jbpm.dao;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import com.thx.common.orm.PaginationDao;
import com.thx.common.util.AppContext;
import com.thx.common.util.ThreadLocalContext;
import com.topdt.jbpm.Constants;
import com.topdt.jbpm.model.Todo;

/**
 * 建立日期 : 2013-4-25 下午12:03:03<br>
 * 作者 : balance<br>
 * 模块 : <br>
 * 描述 : <br>
 * 修改历史: 序号 日期 修改人 修改原因 <br>
 * 1 <br>
 * 2 <br>
 */
@Repository
public class TodoDao extends PaginationDao<Todo> {
	
	public List<Todo> getAll(String userId, String todoTitle){
		String comId = AppContext.getCurrentComId();
		String sql= "select * from wf_todo t where t.to_user_id = ? and t.company_id = ? "
				+ " and t.work_id in( select w.work_id from wf_work w where w.work_state ='running' "
				+ " and w.app_id in (select a.app_id from wf_application a where (a.app_attr='1' or a.app_attr='3' or a.app_attr='5') and a.is_valid='1' ))";
		if(todoTitle!=null){
			sql+=" and t.todo_title like '%" + todoTitle +"%'";
		}
		sql+= " order by t.arrive_time desc";
		List<Object> todos = this.findPageListBySql(Todo.class,false,sql, userId,comId);		
		return convertTodoList(todos);
	}
	
	/**
	 * 重载getAll方法，根据appId查找待办
	 * @author 冯晋首
	 * @date   2015年12月22日 上午9:03:29 
	 * @param userId
	 * @param todoTitle
	 * @param appId
	 * @return
	 */
	public List<Todo> getAll(String userId, String todoTitle, String appId){
		String comId = AppContext.getCurrentComId();
		String sql= "select * from wf_todo t where t.to_user_id = ? and t.company_id = ? "
				+ " and t.work_id in( select w.work_id from wf_work w where w.work_state ='running' ";
		if (StringUtils.isNotBlank(appId) && !appId.equals("null")) {
			if(appId.indexOf(",")==-1){
				sql+= " and w.app_id = '"+appId+"') ";
			}else{
				sql+=" and w.app_id in ("+appId+"))";
			}
		}else{
			sql+= " and w.app_id in (select a.app_id from wf_application a where (a.app_attr='1' or a.app_attr='3' or a.app_attr='5') and a.is_valid='1' ))";
		}
		if(todoTitle!=null){
			sql+=" and t.todo_title like '%" + todoTitle +"%'";
		}
		sql+= " order by t.arrive_time desc";
		List<Object> todos = this.findPageListBySql(Todo.class,false,sql, userId,comId);		
		return convertTodoList(todos);
	}
	
	/**
	 * 分页获取用户所有代办
	 * @author 刘杰
	 * @date   2015年12月28日 下午2:21:46 
	 * @param userId
	 * @param todoTitle
	 * @return
	 */
	public List<Todo> getAllTodo(String userId, String todoTitle){
		String comId = AppContext.getCurrentComId();
		String sql= "select * from wf_todo t where t.to_user_id = ? and t.company_id = ? "
				+ " and t.work_id in( select w.work_id from wf_work w where w.work_state ='running') ";
		if(todoTitle!=null){
			sql+=" and t.todo_title like '%" + todoTitle +"%'";
		}
		sql+= " order by t.arrive_time desc";
		List<Object> todos = this.findPageListBySql(Todo.class,false,sql, userId,comId);		
		return convertTodoList(todos);
	}
	
	/**
	 * 得到待办总数
	 * @author 刘杰
	 * @date   2016年1月20日 下午8:32:36 
	 * @param userId
	 * @return
	 */
	public int getTodoTotalNum(String userId) {
		String comId = AppContext.getCurrentComId();
		String sql= "select count(*) from wf_todo t where t.to_user_id = ? and t.company_id = ? "
				+ " and t.work_id in( select w.work_id from wf_work w where w.work_state ='running') ";
		sql+= " order by t.arrive_time desc";
		Object o = createSQLQuery(sql, userId,comId).uniqueResult();
		if(o!=null){
			return Integer.parseInt(o.toString());
		}
		return 0;
	}
	
	/**
	 * 客户端接口根据参数取得公文待办信息
	 * @author zhangyong
	 * @date   2015年12月23日 上午11:30:16 
	 * @param personnelId
	 * @param appId
	 * @param start
	 * @param max
	 * @param todoTitle
	 * @return
	 */
	public List<Todo> getTodoByAppIdOfDodument(String personnelId, String appId, int start, int max,String todoTitle ) {		
		String comId = AppContext.getCurrentComId();
		String sql= "select  * from wf_todo t where t.to_user_id = ? and t.company_id = ? "
				+ " and t.work_id in( select w.work_id from wf_work w where w.work_state ='running' ";
		if (StringUtils.isNotBlank(appId) && !appId.equals("null")) {
			//fengjinshou 20160217
			if(appId.indexOf(",")==-1){
				sql+= " and w.app_id = '"+appId+"') ";
			}else{
				sql+= " and w.app_id in ("+appId+")) ";
			}
		}else{
			sql+= " and w.app_id in (select a.app_id from wf_application a where (a.app_attr='1' or a.app_attr='3' or a.app_attr='5') and a.is_valid='1' ))";
		}
		if(todoTitle!=null){
			sql+=" and t.todo_title like '%" + todoTitle +"%'";
		}
		sql+= " order by t.arrive_time desc";		
		List<Todo> todos = createSQLQuery(sql, personnelId,comId).addEntity(Todo.class).setFirstResult(start).setMaxResults(max).list();
		return  todos ;	
	
	}		

	private List<Todo> convertTodoList(List<Object> todos){
		List<Todo> todoList = new ArrayList<Todo>();
		for(int i=0;i<todos.size();i++){
			Object todo = todos.get(i);
			todoList.add((Todo)todo);
		}
		return todoList;
	}
	@SuppressWarnings("unchecked")
	public List<Todo> getTodosByState(String userId, String state) {
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = "companyId='" + comId + "' and ";

		String hql = "from Todo where " + condition + " toUserId=? and state=?";
		List<Todo> todos = this.createQuery(hql, userId, state).list();
		return todos;
	}

	public void deleteOthers(String workId, String todoId) {
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = "companyId='" + comId + "' and ";

		String hql = "delete from Todo where " + condition + " workId=? and todoId<>?";
		this.createQuery(hql, workId, todoId).executeUpdate();
	}

	public void delByWorkId(String workId) {
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = "companyId='" + comId + "' and ";

		String hql = "delete from Todo where " + condition + " workId=?";
		this.createQuery(hql, workId).executeUpdate();
	}

	@SuppressWarnings("unchecked")
	public List<Todo> getAllTodos(String userId) {
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = "companyId='" + comId + "' and ";

		String hql = "from Todo where " + condition + " toUserId=?";
		List<Todo> todos = this.createQuery(hql, userId).list();
		return todos;
	}

	@SuppressWarnings("unchecked")
	public Todo findTodoByWorkAndUser(String workId, String userId) {
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = "companyId='" + comId + "' and ";

		String hql = "from Todo where " + condition + " workId=? and toUserId=?";

		List<Todo> todos = this.createQuery(hql, workId, userId).list();
		if (todos != null && todos.size() > 0)
			return todos.get(0);
		return null;
	}

	@SuppressWarnings("unchecked")
	public List<Todo> getTodosByWorkId(String workId) {
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = "companyId='" + comId + "' and ";

		String hql = "from Todo where " + condition + " workId=?";
		List<Todo> todos = this.createQuery(hql, workId).list();
		return todos;
	}

	/**
	 * 查询待办事宜列表 建立日期 : June 21, 2013 <br>
	 * 作者 : 王阿敏<br>
	 * 
	 * @param info
	 * @return
	 */
	/*@SuppressWarnings("unchecked")
	public List<Todo> getAll(Todo todo, Long personnelId, int start, int max) {
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = "todo.company_id='" + comId + "' and ";

		String todoTitle = "";
		if (todo != null && !"".equals(todo) && todo.getTodoTitle() != null && !"".equals(todo.getTodoTitle())) {
			todoTitle = " todo.todo_title like '%" + todo.getTodoTitle() + "%' and ";
		}

		// String sql = "select * from wf_todo todo " + " where " + condition +
		// todoTitle + " todo.to_user_id = ? "
		// +
		// " and not exists (select 1 from wf_work work where todo.work_id= work.work_id"
		// +
		// " and exists (select 1 from wf_application app where app.is_inherent = 1 ))"
		// + " order by todo.arrive_time desc";
		String sql = "select * from wf_todo t where t.to_user_id = ? and t.company_id = ? and t.work_id in("
				+ " select w.work_id from wf_work w where w.work_state ='running' )order by t.arrive_time desc";
		List<Todo> list = createSQLQuery(sql, new Object[] { String.valueOf(personnelId), comId })
				.addEntity(Todo.class).setFirstResult(start).setMaxResults(max).list();
		return list;
	}*/

	/**
	 * 查询待办事宜的总数 建立日期 : June 21, 2013 <br>
	 * 作者 : 王阿敏<br>
	 * 
	 * @param info
	 * @return
	 */
/*	public int queryCount(Todo todo, Long personnelId) {
		String sql = "select count(*) " + getSql(todo, personnelId);
		List list = createSQLQuery(sql, String.valueOf(personnelId)).list();
		if (list.size() > 0) {
			return new Integer(list.get(0).toString());
		}
		return 0;
	}*/

	/**
	 * 查询sql语句 建立日期 : Apr 18, 2013 <br>
	 * 作者 : 王阿敏<br>
	 * 
	 * @param info
	 * @return
	 */
	public String getSql(Todo todo, Long personnelId) {
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = "todo.company_id='" + comId + "' and ";

		String todoTitle = "";
		if (todo != null && !"".equals(todo) && todo.getTodoTitle() != null && !"".equals(todo.getTodoTitle())) {
			todoTitle = " todo.todo_title like '%" + todo.getTodoTitle() + "%' and ";
		}
		String sql = " from wf_todo todo " + " where " + condition + todoTitle + " todo.to_user_id = ? "
				+ " and not exists (select 1 from wf_work work where todo.work_id= work.work_id"
				+ " and exists (select 1 from wf_application app where app.is_inherent = 1 ))"
				+ " order by todo.arrive_time desc";
		return sql;
	}

	public List<String> getActivityNamesByworkId(String workId) {
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = "companyId='" + comId + "' and ";

		String hql = "select activityName from Todo where " + condition + " workId=?";
		List<String> activityNames = this.createQuery(hql, workId).list();
		return activityNames;
	}

	/**
	 * 
	 * @author wangamin
	 * @date 2013-4-17 上午11:23:40
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Todo> getTodoByPid(Long personnelId,int num) {
//		String comId = AppContext.getCurrentComId();
//		String condition = "";
//		if (comId != null && !"".equals(comId))
//			condition = "t.company_id='" + comId + "' and w.company_id='" + comId + "'  and ";
//
//		String sql = "select t.todo_id, t.arrive_time, t.from_user,t.activity_name, t.task_id, t.to_user_id, t.work_id, "
//				+ "t.state, t.batch, t.seq_num, t.todo_mode, t.dest_activity_name, t.app_name, "
//				+ "t.pre_activity_name,  t.company_id,w.work_title todo_title,t.is_send " + "from wf_todo t,wf_work w where "
//				+ condition + " t.to_user_id=? and w.work_state='running' and t.work_id=w.work_id "
//				+ "order by t.arrive_time desc";
//
//		List<Todo> list = createSQLQuery(sql, String.valueOf(personnelId)).addEntity(Todo.class).setFirstResult(0)
//				.setMaxResults(num).list();
//		return list;
		String comId = AppContext.getCurrentComId();
		String sql= "select  * from wf_todo t where t.to_user_id = ? and t.company_id = ? "
				+ " and t.work_id in( select w.work_id from wf_work w where w.work_state ='running' )";

		sql+= " order by t.arrive_time desc";		
		List<Todo> todos = createSQLQuery(sql, personnelId.toString(),comId).addEntity(Todo.class).setFirstResult(0).setMaxResults(num).list();
		return  todos ;		
	}

	/**
	 * 通过appId获取对应的待办工作
	 * 
	 * @param personnelId
	 * @param appId
	 * @param start
	 * @param max
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Todo> findByWorkId(Todo todo, Long personnelId, String appId, int start, int max) {
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = "t.company_id='" + comId + "' and ";

		String todoTitle = "";
		if (todo != null && !"".equals(todo) && todo.getTodoTitle() != null && !"".equals(todo.getTodoTitle())) {
			todoTitle = " t.todo_title like '%" + todo.getTodoTitle() + "%' and ";
		}
		String sql = "select t.* from wf_todo t left outer join wf_work work on t.work_id = work.work_id" + " where "
				+ condition + todoTitle + " t.to_user_id=? and work.app_id=? order by t.arrive_time desc";
		List<Todo> list = createSQLQuery(sql, new Object[] { String.valueOf(personnelId), appId })
				.addEntity(Todo.class).setFirstResult(start).setMaxResults(max).list();
		return list;
	}

	/**
	 * 通过appId获取待办工作的数量
	 * 
	 * @param personnelId
	 * @param appName
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public int queryCountByWorkId(Todo todo, Long personnelId, String appId) {
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = "t.company_id='" + comId + "' and ";

		String todoTitle = "";
		if (todo != null && !"".equals(todo) && todo.getTodoTitle() != null && !"".equals(todo.getTodoTitle())) {
			todoTitle = " t.todo_title like '%" + todo.getTodoTitle() + "%' and ";
		}
		String sql = "select count(*) from wf_todo t left outer join wf_work work on t.work_id = work.work_id"
				+ " where " + condition + todoTitle + " t.to_user_id=? and work.app_id=? order by t.arrive_time desc";
		List list = createSQLQuery(sql, new Object[] { String.valueOf(personnelId), appId }).list();
		if (list.size() > 0) {
			return Integer.parseInt(list.get(0).toString());
		}
		return 0;
	}

	/**
	 * 客户端工单管理查询待办
	 * @author zhangyong
	 * @date   2015年12月23日 下午2:38:19 
	 * @param userId
	 * @param appId
	 * @param todoTitle
	 * @param start
	 * @param max
	 * @return
	 */
	public List<Todo> getTodoPageOfTicket(String userId, String appId,String todoTitle,int start, int max) {
		List<Todo> todos;
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (StringUtils.isNotBlank(todoTitle)) {
			condition = "and  t.todo_title like '%" + todoTitle + "%'";
		}
		String sql = " select * from wf_todo t where t.company_id = ? and t.to_user_id = ?  " + condition
				+ " and t.work_id in ( select w.work_id from wf_work w where w.work_state = 'running' ";
		if (StringUtils.isNotBlank(appId) && !appId.equals("null")) {
			//fengjinshou 20160222
			if(appId.indexOf(",")==-1){
				sql += " and w.app_id = ?) ";
			}else{
				sql += " and w.app_id in ("+appId+")) ";
			}
		}else{
			sql += " and w.app_id  in (select a.app_id from wf_application a where (a.app_attr='0' or a.app_attr='2' or a.app_attr='4') and a.is_valid='1' ))";
		}
		
		sql += " order by t.arrive_time desc";
		if (StringUtils.isNotBlank(appId) && !appId.equals("null") ) {
			if(appId.indexOf(",")==-1){
				todos = this.createSQLQuery(sql, new Object[] { comId, userId, appId }).addEntity(Todo.class).setFirstResult(start).setMaxResults(max).list();
			}else{
				todos = this.createSQLQuery(sql, new Object[] { comId, userId}).addEntity(Todo.class).setFirstResult(start).setMaxResults(max).list();
			}
		} else {
			todos = this.createSQLQuery(sql, new Object[] { comId, userId}).addEntity(Todo.class).setFirstResult(start).setMaxResults(max).list();
		}
		return todos;
	}
	public List<Todo> getTodoPage(String userId, String appId,String todoTitle) {
		List<Object> todos;
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (StringUtils.isNotBlank(todoTitle)) {
			condition = "and  t.todo_title like '%" + todoTitle + "%'";
		}
		String sql = " select * from wf_todo t where t.company_id = ? and t.to_user_id = ?  " + condition
				+ " and t.work_id in ( select w.work_id from wf_work w where w.work_state = 'running' ";
		if (StringUtils.isNotBlank(appId) && !appId.equals("null")) {
			sql += " and w.app_id = ?) ";
		}else{
			sql += " and w.app_id not in (select a.app_id from wf_application a where (a.app_attr='1' or a.app_attr='3' or a.app_attr='5') and a.is_valid='1' ))";
		}
		
		sql += " order by t.arrive_time desc";
		if (StringUtils.isNotBlank(appId) && !appId.equals("null") ) {
			todos = this.findPageListBySql(Todo.class,false,sql, new Object[] { comId, userId, appId });
		} else {
			todos = this.findPageListBySql(Todo.class,false,sql, new Object[] { comId, userId});
		}
		return this.convertTodoList(todos);
	}
	/**
	 * 
	 * 建立日期 : Aug 5, 2013 4:13:45 PM<br>
	 * 作者: 孙耀飞<br>
	 * 模块: 获得代办的Todo集合，分页查询的数量<br>
	 * 描述: <br>
	 * 1 <br>
	 * 2 <br>
	 */
	@SuppressWarnings("unchecked")
	public int getTodoCount(Todo todo, Long personnelId, String appId, String isExpand) {
		String comId = AppContext.getCurrentComId();
		String todoTitle = "";
		if (StringUtils.isNotBlank(todo.getTodoTitle())) {
			todoTitle = "and  t.todo_title like '%" + todo.getTodoTitle() + "%'";
		}
		List list = new ArrayList<Todo>();
		String sql = " select count(*) from wf_todo t where t.company_id = ? and t.to_user_id = ?  " + todoTitle
				+ "and t.work_id in (" + " select w.work_id from wf_work w where  w.work_state = 'running' ";
		if (StringUtils.isNotBlank(appId) && !appId.equals("null") ) {
			sql += "and w.app_id = ? ";
		}
		if (StringUtils.isNotBlank(isExpand) && isExpand.equals("yes")) {
			sql += " and w.app_id not in(select app.app_id from wf_application app where app.is_inherent='1' and app.company_id='"
					+ comId + "')";
		}
		sql += ") order by t.arrive_time desc";
		if (StringUtils.isNotBlank(appId) && !appId.equals("null")) {
			list = createSQLQuery(sql, new Object[] { comId, String.valueOf(personnelId), appId }).list();
		} else {
			list = createSQLQuery(sql, new Object[] { comId, String.valueOf(personnelId) }).list();
		}

		if (list.size() > 0) {
			java.math.BigDecimal count = (java.math.BigDecimal) list.get(0);
			return count.intValue();
		}
		return 0;
	}	


	/**
	 * 根据应用ID与用户ID取得待办信息最新总数
	 * 
	 * @param appId
	 * @param userId
	 * @return
	 */
	public int getTodoCount(Long uId, String appIds, Date endDate, Date startdate) {
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = "t.company_id='" + comId + "' and ";

		String sql = "select count(1) from wf_todo t left outer join wf_work work on t.work_id = work.work_id"
				+ " where " + condition + " t.to_user_id=? and work.app_id in (" + appIds
				+ ") and  work.work_state = 'running' and t.arrive_time>? " + "  and t.arrive_time<? ";
		List list = createSQLQuery(sql, new Object[] { String.valueOf(uId), startdate, endDate }).list();
		if (list.size() > 0) {
			return Integer.parseInt(list.get(0).toString());
		}
		return 0;
	}

	/**
	 * 根据应用ID与用户ID取得待办信息最新总数
	 * 
	 * @param appId
	 * @param userId
	 * @return
	 */
	public int getTodoCount(Long uId, String appIds) {
		String comId = AppContext.getCurrentComId();
		String conditionT = "";
		String conditionW = "";
		if (comId != null && !"".equals(comId)) {
			conditionT = "t.company_id='" + comId + "' and ";
			conditionW = "w.company_id='" + comId + "' and ";
		}

		String sql = "select count(1) from wf_todo t  where " + conditionT + " t.to_user_id=? and "
				+ "exists (select 1 from wf_work w where " + conditionW + " w.work_state = 'running'  and w.app_id in (" + appIds
				+ ") and w.work_id=t.work_id)";

		List list = createSQLQuery(sql, new Object[] { String.valueOf(uId) }).list();
		if (list.size() > 0) {
			return Integer.parseInt(list.get(0).toString());
		}
		return 0;
	}

	@SuppressWarnings("unchecked")
	public List<Todo> findWorkRecordFromTodo(String workId) {
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = "todo.companyId='" + comId + "' and ";
		String hql = "from Todo todo where " + condition + " todo.workId = ? order by todo.arriveTime";
		List<Todo> list = createQuery(hql, new Object[] { workId }).list();
		return list;
	}

	public int countExtTodo(String uId) {

		String comId = AppContext.getCurrentComId();
		String conditionT = "";
		String conditionW = "";
		String conditionA = "";
		if (comId != null && !"".equals(comId)) {
			conditionT = "t.company_id='" + comId + "' and ";
			conditionW = "w.company_id='" + comId + "' and ";
			conditionA = "a.company_id='" + comId + "' and ";
		}

		String sql = "select count(1) from wf_todo  t where  " + conditionT
				+" t.to_user_id=? and exists (select 1 from wf_work w where  " + conditionW
				+ " w.work_state='running' and exists (select 1 from wf_application a where " + conditionA
				+ " a.is_inherent='0' and a.app_id=w.app_id) and w.work_id=t.work_id ) ";

		List list = createSQLQuery(sql, new Object[] { String.valueOf(uId) }).list();
		if (list.size() > 0) {
			return Integer.parseInt(list.get(0).toString());
		}
		return 0;

	}

	public List<Todo> listTodos(String userId, String appId, String isInherent, int start, int max) {

		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = "t.company_id='" + comId + "' and w.company_id='" + comId + "'  and ";

		String sql = null;
		if (appId != null && !"".equals(appId.trim()) && !"null".equals(appId)) {
			sql = "select t.todo_id, t.arrive_time, t.from_user,t.activity_name, t.task_id, t.to_user_id, t.work_id, "
					+ "t.state, t.batch, t.seq_num, t.todo_mode, t.dest_activity_name, t.app_name, "
					+ "t.pre_activity_name,t.company_id,w.work_title todo_title,t.is_send from wf_todo t,wf_work w where "
					+ condition + " t.to_user_id=? and w.work_state='running' and w.app_id ='" + appId
					+ "' and t.work_id=w.work_id  order by t.arrive_time desc";
		} else {
			sql = "select t.todo_id, t.arrive_time, t.from_user,t.activity_name, t.task_id, t.to_user_id, t.work_id,t.state, "
					+ "t.batch, t.seq_num, t.todo_mode, t.dest_activity_name, t.app_name, t.pre_activity_name,  t.company_id,"
					+ "w.work_title todo_title,t.is_send from wf_todo t,wf_work w where "
					+ condition
					+ " t.to_user_id=? and w.work_state='running' and "
					+ "exists (select 1 from wf_application a where a.is_inherent='"
					+ isInherent
					+ "' and a.app_id=w.app_id ) " + "and t.work_id=w.work_id order by t.arrive_time desc";
		}

		List<Todo> list = createSQLQuery(sql, userId).addEntity(Todo.class).setFirstResult(start).setMaxResults(max)
				.list();

		return list;

	}

	/**
	 * 根据work_id删除wf_todo表中记录
	 * @author 刘杰
	 * @date   May 16, 2014 3:52:18 PM 
	 * @param delId
	 */
	public void deleteworks(String delIdsStr) {
		String sql = "delete from wf_todo where work_id    in   ("+delIdsStr+") ";
		createSQLQuery(sql).executeUpdate();
	}

	/**
	 * 查找有待办工作的人的id
	 * @author 刘杰
	 * @date   Oct 17, 2014 10:15:49 AM 
	 * @return
	 */
	public String[][] findUserByTodo() {
		String comId = AppContext.getCurrentComId();
		String condition = "";	
		if (comId != null && !"".equals(comId))
			condition = "t.company_id='" + comId + "' and w.company_id='" + comId + "'  and ";

		String sql = "select t.to_user_id, "
				+ "count(*) from wf_todo t,wf_work w where "
				+ condition + " w.work_state='running' and t.work_id=w.work_id "
				+ "group by t.to_user_id";
		
		List<Object[]> list = createSQLQuery(sql).list();
		String[][]  users =new String[list.size()][2];	
		if(list!=null && list.size()>0){
			for(int i=0;i<list.size();i++){	
				for(int j=0;j<users.length;j++){
					users[i][0]=(String)list.get(i)[0];
					users[i][1]=list.get(i)[1].toString();
				}			
			}
		}
		return users;
	}

	/**
	 * 查询所有超时的待办工作
	 * @author LCX
	 * @date   20141212
	 * @param nowTime
	 * @return
	 */
	public List<Todo> getTodoByArriveTime(String nowTime) {
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
		{
			condition = "t.company_id='" + comId + "' and w.company_id='" + comId + "'  and ";
		}
		String sql = "select t.todo_id, t.arrive_time, t.from_user,t.activity_name, t.task_id, t.to_user_id, t.work_id, "
				+ "t.state, t.batch, t.seq_num, t.todo_mode, t.dest_activity_name, t.app_name, "
				+ "t.pre_activity_name,  t.company_id,w.work_title todo_title, t.is_send " + "from wf_todo t,wf_work w where "
				+ condition + " " +
						" t.is_send is null " +
						"and w.work_state='running' and t.work_id=w.work_id and t.is_send is null and round(to_number(to_date('"+nowTime+"','yyyy-MM-dd hh24:mi:ss')-t.arrive_time)*24)>"+24
				+ " order by t.arrive_time desc";
		List<Todo> list = createSQLQuery(sql).addEntity(Todo.class).list();
		return list;
	}
	
	/**
	 * 得到兄弟待办（同一个工作，同一个活动的其他待办）
	 * @author zhangyong
	 * @date   2014-11-27 下午05:10:43 
	 * @param todoId
	 * @param workId
	 * @param activityName
	 * @return
	 */
	public List<Todo>  getOtherTodo(String todoId,String workId ,String activityName,long batch){
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = "companyId='" + comId + "' and ";
		String hql = "from Todo where " + condition + " workId=? and activityName = ? and todoId <> ?  and batch=?";
		List<Todo> todos = this.createQuery(hql, workId, activityName,todoId,batch).list();	
		return todos;		
	}
	
	/**
	 * 更新数据库
	 * @author LCX
	 * @date 20141209
	 * @param personnelId
	 * @param nowTime
	 * @return
	 */
	public void updateIsSendByWorkId(String todoId) {
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
		{
			condition = " t.company_id='" + comId + "'  and ";
		}
		String sql = "update wf_todo t set t.is_send = '"+Constants.IS_SEND+"' where "+condition+"t.todo_id = '"+todoId+"' ";
		createSQLQuery(sql).executeUpdate();
	}

	/**
	 * 根据workId查找待办
	 * @author 刘杰
	 * @date   2016年1月8日 下午8:26:51 
	 * @param workId
	 * @return
	 */
	public List<Todo> findTodosByWorkId(String workId) {
		String hql = "from Todo where workId='"+workId+"'";
		List<Todo> todos = this.createQuery(hql).list();
		return todos;
	}
	

}
